﻿// -----------------------------------------------------------------------
// <copyright file="InstallSqlCmd.cs" company="MSIT">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

namespace DeploymentCustomActions
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Deployment.WindowsInstaller;
    using System.Globalization;
    using System.Data.SqlClient;

    [Serializable]
    public class SqlCmdInstances
    {
        public int Type { get; set; }
        public string Source { get; set; }
        public int Sequence { get; set; }
        public string Condition { get; set; }
    }

    [Serializable]
    public class SqlCmdInfo
    {
        public SqlCmdInfo() { SqlCmdInstances = new List<SqlCmdInstances>(); SqlCmdVariables = new List<SqlCmdVariable>(); }
        public string ComponentName { get; set; }
        public string ConnectionString { get; set; }
        public List<SqlCmdInstances> SqlCmdInstances { get; set; }
        public List<SqlCmdVariable> SqlCmdVariables { get; set; }         
    }

    public class InstallSqlCmd
    {
        [CustomAction]
        public static ActionResult CreateSqlCmdExecute(Session session)
        {
            
            if (session == null) return ActionResult.Failure;

            List<SqlCmdInfo> databases = ParseSqlCmdTable(session);

            var databasesToInstall = databases.Join(session.Components, s => s.ComponentName, s => s.Name, (x, y) => new { x, y })
                                              .Where(s => s.y.RequestState == InstallState.Local)
                                              .Select(s => s.x);
            
            if (databasesToInstall.Any())
            {
                CustomActionData customActionData = new CustomActionData();
                customActionData.AddObject(Constants.SqlCmd.SqlCmdMetaData, databasesToInstall.ToList());

                session.DoAction("DeploySqlCmdDeferred", customActionData);
            }
            return ActionResult.Success;
        }

        [CustomAction]
        public static ActionResult RemoveSqlCmdExecute(Session session)
        {
            if (session == null) return ActionResult.Failure;

            List<SqlCmdInfo> databases = ParseSqlCmdTable(session);

            var databasesToUninstall = databases.Join(session.Components, s => s.ComponentName, s => s.Name, (x, y) => new { x, y })
                                              .Where(s => s.y.CurrentState == InstallState.Local && s.y.RequestState == InstallState.Absent)
                                              .Select(s => s.x);

            if (databasesToUninstall.Any())
            {
                CustomActionData customActionData = new CustomActionData();
                customActionData.AddObject(Constants.SqlCmd.SqlCmdMetaData, databasesToUninstall.ToList());

                session.DoAction("UninstallSqlCmdDeferred", customActionData);
            }
            return ActionResult.Success;
        }

        [CustomAction]
        public static ActionResult DeploySqlCmdDeferred(Session session)
        {
            if (session == null) return ActionResult.Failure;

            List<SqlCmdInfo> databases = session.CustomActionData.GetObject<List<SqlCmdInfo>>(Constants.SqlCmd.SqlCmdMetaData);

            if (databases.Any(s => ExecuteSqlCommand(s) != 0)) return ActionResult.Failure;

            return ActionResult.Success;
        }

        [CustomAction]
        public static ActionResult UninstallSqlCmdDeferred(Session session)
        {
            if (session == null) return ActionResult.Failure;

            List<SqlCmdInfo> databases = session.CustomActionData.GetObject<List<SqlCmdInfo>>(Constants.SqlCmd.SqlCmdMetaData);
            if (databases.Any(s => ExecuteSqlCommand(s) != 0)) return ActionResult.Failure;

            return ActionResult.Success;
        }

        private static int ExecuteSqlCommand(SqlCmdInfo sqlCmdInfo)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlCmdInfo.ConnectionString))
            {
                sqlConnection.Open();
                using (SqlTransaction transaction = sqlConnection.BeginTransaction())
                {
                    foreach (var command in sqlCmdInfo.SqlCmdInstances.OrderBy(s => s.Sequence).Select(s => new {s.Source, s.Type }))
                    {
                        SqlCommand sqlCommand = new SqlCommand()
                        {
                            CommandText = command.Type == 2 ? command.Source : GetFileContent(command.Source),
                            CommandType = System.Data.CommandType.Text,
                            Connection = sqlConnection,
                            Transaction = transaction
                        };
                        sqlCmdInfo.SqlCmdVariables.Select(s => sqlCommand.Parameters.Add(new SqlParameter(s.Name, s.Value))).ToList();
                        sqlCommand.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return 0;
        }

        private static string GetFileContent(string filePath)
        {
            return "SELECT 1";
        }

        private static List<SqlCmdInfo> ParseSqlCmdTable(Session session)
        {
            Dictionary<string, SqlCmdInfo> result = new Dictionary<string, SqlCmdInfo>();
            using (View view = session.Database.OpenView(Constants.SqlCmd.Query))
            {
                view.Execute();
                foreach (Record record in view) using (record)
                    {
                        string id = record.GetString(Constants.SqlCmd.SqlCmdId);
                        SqlCmdInfo sqlCmdInfo = null;

                        if (!result.TryGetValue(id, out sqlCmdInfo))
                        {
                            string componenetName = session.Format(record.GetString(Constants.SqlCmd.ComponentName));
                            string connectionString = session.Format(record.GetString(Constants.SqlCmd.ConnectionString));

                            sqlCmdInfo = new SqlCmdInfo()
                            {
                                ComponentName = componenetName,
                                ConnectionString = connectionString
                            };
                            result.Add(id, sqlCmdInfo);
                        }

                        int queryType = record.GetInteger(Constants.SqlCmd.QueryType);
                        string queryString = session.Format(record.GetString(Constants.SqlCmd.QueryString));
                        int sequence = record.GetInteger(Constants.SqlCmd.Sequence);
                        string condition = session.Format(record.GetString(Constants.SqlCmd.Condition));

                        SqlCmdInstances sqlCmdInstance = new SqlCmdInstances()
                        {
                            Source = queryString,
                            Type = queryType,
                            Sequence = sequence,
                            Condition = condition
                        };

                        if (session.EvaluateCondition(condition, true))
                        {
                            sqlCmdInfo.SqlCmdInstances.Add(sqlCmdInstance);
                        }
                    }
            }
            ParseSqlCmdVariables(session, ref result);
            return result.Values.ToList();
        }

        private static void ParseSqlCmdVariables(Session session, ref Dictionary<string, SqlCmdInfo> factory)
        {
            using (View view = session.Database.OpenView(Constants.SqlCmd.SqlCmdVariablesQuery))
            {
                view.Execute();
                foreach (Record record in view) using (record)
                    {
                        string sqlCmdId = record.GetString(Constants.SqlCmd.SqlCmdId);
                        string name = session.Format(record.GetString(Constants.SqlCmd.Name));
                        string value = session.Format(record.GetString(Constants.SqlCmd.Value));
                        
                        SqlCmdVariable sqlCmdVariable = new SqlCmdVariable()
                        {
                            Name = name,
                            Value = value
                        };
                        factory[sqlCmdId].SqlCmdVariables.Add(sqlCmdVariable);
                    }
            }
        }
    }    
}
